--    Program: 		CAPPOEXT_DOCUMENT_TYPE.sql
--    Description:	Extract PO Document Type information 
--    Revision:		1st     
--    Parameter: 	1. set of books short name e.g. SOB_C999

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;
alter session set nls_date_format='YYYYMMDD HH24:MI:SS';

declare   
   l_processed_count_1		number;
   l_rejected_count_1 		number;
   l_processed_count_2		number;
   l_rejected_count_2 		number;

   l_err_msg				varchar2(2000);
                    		
   l_utl_file_1       		utl_file.file_type;
   l_utl_file_2       		utl_file.file_type;
   l_filename_1       		varchar2(255);
   l_filename_2       		varchar2(255);
   l_pathname       		varchar2(100);
                    		
   l_ngo_sob				varchar2(150);
   l_ngo_sob_id             number;
   
   cursor cur_b (p_set_of_books_id number) is         
      select pdt.* 
      from   po_document_types_all_b pdt,
             hr_organization_information hoi
      where  pdt.org_id = hoi.organization_id
      and    hoi.org_information_context = 'Operating Unit Information'
      and    hoi.org_information3 = to_char(p_set_of_books_id);
      
   cursor cur_tl (p_document_type_code varchar, p_document_subtype varchar, p_org_id number) is         
      select * 
      from   po_document_types_all_tl
      where  document_type_code = p_document_type_code
      and    document_subtype = p_document_subtype
      and    org_id = p_org_id;   


begin
   l_pathname := '/tmp';
   l_filename_1 := 'CAPPO_DOCUMENT_TYPE_B.dat';
   l_filename_2 := 'CAPPO_DOCUMENT_TYPE_TL.dat';
   l_utl_file_1 := utl_file.fopen(l_pathname, l_filename_1, 'w');
   l_utl_file_2 := utl_file.fopen(l_pathname, l_filename_2, 'w');
   
   l_processed_count_1 := 0;
   l_rejected_count_1  := 0;   
   l_processed_count_2 := 0;
   l_rejected_count_2  := 0;
   l_err_msg := '';
   
   l_ngo_sob := '&1';
   
   begin
    select set_of_books_id
    into l_ngo_sob_id
    from gl_sets_of_books 
    where upper(short_name)=upper(l_ngo_sob);
  exception
     when others then
        dbms_output.put_line(l_ngo_sob||': set of books name not found');
  end;

   for cb in cur_b(l_ngo_sob_id) loop   		  
     utl_file.put_line(l_utl_file_1,   
         cb.DOCUMENT_TYPE_CODE              || chr(9) ||
         cb.DOCUMENT_SUBTYPE                || chr(9) ||
         cb.LAST_UPDATE_DATE                || chr(9) ||
         cb.LAST_UPDATED_BY                 || chr(9) ||
         cb.CREATED_BY                      || chr(9) ||
         cb.CREATION_DATE                   || chr(9) ||
         cb.ARCHIVE_EXTERNAL_REVISION_CODE  || chr(9) ||
         cb.CAN_PREPARER_APPROVE_FLAG       || chr(9) ||
         cb.FORWARDING_MODE_CODE            || chr(9) ||
         cb.CAN_CHANGE_FORWARD_FROM_FLAG    || chr(9) ||
         cb.CAN_APPROVER_MODIFY_DOC_FLAG    || chr(9) ||
         cb.CAN_CHANGE_APPROVAL_PATH_FLAG   || chr(9) ||
         cb.CAN_CHANGE_FORWARD_TO_FLAG      || chr(9) ||
         cb.QUOTATION_CLASS_CODE            || chr(9) ||
         cb.DEFAULT_APPROVAL_PATH_ID        || chr(9) ||
         cb.LAST_UPDATE_LOGIN               || chr(9) ||
         cb.ATTRIBUTE_CATEGORY              || chr(9) ||
         cb.ATTRIBUTE1                      || chr(9) ||
         cb.ATTRIBUTE2                      || chr(9) ||
         cb.ATTRIBUTE3                      || chr(9) ||
         cb.ATTRIBUTE4                      || chr(9) ||
         cb.ATTRIBUTE5                      || chr(9) ||
         cb.ATTRIBUTE6                      || chr(9) ||
         cb.ATTRIBUTE7                      || chr(9) ||
         cb.ATTRIBUTE8                      || chr(9) ||
         cb.ATTRIBUTE9                      || chr(9) ||
         cb.ATTRIBUTE10                     || chr(9) ||
         cb.ATTRIBUTE11                     || chr(9) ||
         cb.ATTRIBUTE12                     || chr(9) ||
         cb.ATTRIBUTE13                     || chr(9) ||
         cb.ATTRIBUTE14                     || chr(9) ||
         cb.ATTRIBUTE15                     || chr(9) ||
         cb.SECURITY_LEVEL_CODE             || chr(9) ||
         cb.ACCESS_LEVEL_CODE               || chr(9) ||
         cb.DISABLED_FLAG                   || chr(9) ||
         cb.REQUEST_ID                      || chr(9) ||
         cb.PROGRAM_APPLICATION_ID          || chr(9) ||
         cb.PROGRAM_ID                      || chr(9) ||
         cb.PROGRAM_UPDATE_DATE             || chr(9) ||
         cb.ORG_ID                          || chr(9) ||
         cb.WF_APPROVAL_ITEMTYPE            || chr(9) ||
         cb.WF_APPROVAL_PROCESS             || chr(9) ||
         cb.WF_CREATEDOC_ITEMTYPE           || chr(9) ||
         cb.WF_CREATEDOC_PROCESS                 
	 );
	 
	 for ctl in cur_tl(cb.document_type_code, cb.document_subtype, cb.org_id) loop   		  
       utl_file.put_line(l_utl_file_2,   
         ctl.DOCUMENT_TYPE_CODE  ||chr(9)||
         ctl.DOCUMENT_SUBTYPE    ||chr(9)||
         ctl.ORG_ID              ||chr(9)||         
         ctl.LANGUAGE            ||chr(9)||
         ctl.SOURCE_LANG         ||chr(9)||
         ctl.TYPE_NAME           ||chr(9)||
         ctl.LAST_UPDATE_DATE    ||chr(9)||
         ctl.LAST_UPDATED_BY     ||chr(9)||
         ctl.CREATED_BY          ||chr(9)||
         ctl.CREATION_DATE       ||chr(9)||
         ctl.LAST_UPDATE_LOGIN
       );
     
       l_processed_count_2 := l_processed_count_2 + 1;
     end loop;

	 l_processed_count_1 := l_processed_count_1 + 1;
   end loop;
   
   l_err_msg := '';
   if l_processed_count_1 = 0 then   
     l_rejected_count_1 := l_rejected_count_1 + 1;
     l_err_msg := l_ngo_sob || ': Document Type not found';
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
   values ('CAPPOEXT_DOCUMENT_TYPE_B', sysdate, l_processed_count_1, l_rejected_count_1, l_processed_count_1-l_rejected_count_1, l_err_msg );
   
   l_err_msg := '';
   if l_processed_count_2 = 0 then   
     l_rejected_count_2 := l_rejected_count_2 + 1;
     l_err_msg := l_ngo_sob || ': Document Type not found';
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
   values ('CAPPOEXT_DOCUMENT_TYPE_TL', sysdate, l_processed_count_2, l_rejected_count_2, l_processed_count_2-l_rejected_count_2, l_err_msg );
     
   commit;

   utl_file.fclose(l_utl_file_1); 
   utl_file.fclose(l_utl_file_2); 
exception 
  when others then
    dbms_output.put_line(sqlerrm); 
end;
/

--exit;
